package DB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import Model.Invoice;


public class DBInvoice implements IFDBInvoice {
	private static Connection con;
	Statement stmt;
	public DBInvoice() 
	{
		con = DBCon.getInstance().getDBCon();
	}
	/* (non-Javadoc)
	 * @see DB.IFBDInvoice#createInvoice(int, java.lang.String, int, int)
	 */
	@Override
	public int createInvoice(String date,int cID ,int coID,int chID, int price )
	{ int iID=-1;
		try{
			iID=GetMax.getMaxId("Select max(iID) from Invoice")+1;//Gets the maximum iID from the DB and adds+1 for the next ID
			String query ="INSERT INTO Invoice(iID, date, cID, coID, chID, price)  VALUES('"+iID+"','"+date+"','"+cID+"','"+coID+"','"+chID+"','"+price+"')";
			 Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
			}
		return iID;
	}
	/* (non-Javadoc)
	 * @see DB.IFBDInvoice#deleteInvoice(int)
	 */
	@Override
	public void deleteInvoice(int iID)
	{	//iID=1; 
		try{
			String query="DELETE FROM Invoice WHERE iID = '"+iID+"'";
			Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  stmt.executeUpdate(query);
	          stmt.close();
		}
		catch(Exception e)
		{e.printStackTrace();
		}
	}
	/* (non-Javadoc)
	 * @see DB.IFBDInvoice#updateInvoice(Model.Invoice)
	 */
	@Override
	public void updateInvoice(Invoice g, int choice)
	{ Invoice suObj = g;
	boolean exec =false;
	String query="";
	
	switch(choice){
	case 0: //Update Date
		query="UPDATE Invoice SET "+
			 	  "date ='"+ suObj.getDate()+"'"+
			 	  " WHERE cID = '"+ suObj.getcID() + "'";
			 	 exec =true;
			 	 break;
	case 1: //Update cID
		query="UPDATE Invoice SET "+
			 	  "cID ='"+ suObj.getcID()+"'"+
			 	  " WHERE cID = '"+ suObj.getcID() + "'";
			 	 exec =true;
			 	 break;
	case 2://Update Court
		query="UPDATE Invoice SET "+
				"coID ='"+ suObj.getCoID()+"'"+
			 	 " WHERE cID = '"+ suObj.getcID() + "'";
			 	 exec =true;
			 	 break;
	case 3://Update Coach
		query="UPDATE Invoice SET "+
				"chID ='"+ suObj.getchID()+"'"+
			 	 " WHERE cID = '"+ suObj.getcID() + "'";
			 	 exec =true;
			 	 break;

	case 5://Update Price
		query="UPDATE Invoice SET "+
				"price ='"+ suObj.getPrice()+"'"+
			 	 " WHERE cID = '"+ suObj.getcID() + "'";
		exec=true;
		break;

	case 6://Update All
		query="UPDATE Invoice SET "+
			 	  "date ='"+ suObj.getDate()+"', "+
			 	  "cID ='"+ suObj.getcID() + "'," +
			 	 "coID ='"+ suObj.getCoID() + "'," +
			 	"chID ='"+ suObj.getchID() + "'," +
			 	"price ='"+ suObj.getPrice() + "'" +
			          " WHERE iID = '"+ suObj.getiID() + "'";
		exec=true;
		break;}
	
	
	while(exec)
	{ exec =false;
              try{
            	  Statement stmt = con.createStatement();
            	  stmt.setQueryTimeout(5);
            	  stmt.executeUpdate(query);
            	  stmt.close();
            	  
              }
              catch(Exception e)
              {e.printStackTrace();}
	}
	}
	
	private String buildQuery()
	{
	    String query="SELECT iID, date, cID, coID, chID, price  FROM Invoice";
		/*
		if (wClause.length()>0)
			query=query+" WHERE ";
			*/
		return query;
	}
	/* (non-Javadoc)
	 * @see DB.IFBDInvoice#miscWhere()
	 */
	@Override
	public ArrayList<Invoice> miscWhere()
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery();
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	 Invoice iObj = new Invoice();
		         iObj = buildInvoice(results);	
                 list.add(iObj);	
		}//end while
                 stmt.close();     
                 
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	/* (non-Javadoc)
	 * @see DB.IFBDInvoice#findInvoice(int)
	 */
	@Override
	public Invoice findInvoice(int iID)
	{
		ResultSet results;
		Invoice iObj = new Invoice();
                
	        String query =  buildQuery() + " WHERE iID ="+"'"+iID+"'";
                System.out.println(query);
		try{ // 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            iObj = buildInvoice(results);
                            
                            stmt.close();
                            
                            
			}
                        else{ //no Invoice was found
                            iObj = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return iObj;
	}
	private Invoice buildInvoice(ResultSet results)
    {   Invoice suObj = new Invoice();
        try{ // the columns from the table glayee  are used
              suObj.setiID(results.getInt("iID"));
              suObj.setDate(results.getString("date"));
              suObj.setcID(results.getInt("cID"));
              suObj.setCoID(results.getInt("coID"));
              suObj.setchID(results.getInt("chID"));
              suObj.setPrice(results.getInt("price"));

              
        }
       catch(Exception e)
       {
           System.out.println("error in building the Invoice object");
       }
       return suObj;
}
	
}
